# -*-  coding:utf-8 -*- 
# Author : lihongliang

from tabulate import tabulate
import os

DB_FILE="staff.db"
COLUMNS= ['id','name','age','phone','dept','enrolled_date']

def print_log(msg,log_type = "info"):
    if  log_type == 'info':
        print("\033[32;1m %s \033[0m" %msg)
    elif log_type == 'error':
        print("\033[31;1m %s \033[0m" %msg)

def load_db(db_file):
    #
    # 加载员工信息， 转化成指定模式
    # :param db_file:
    # :return:
    #
    data = {}
    for i in COLUMNS:
        data[i] = []

    f = open(db_file,"r",encoding='utf-8')
    for line in  f:
        staff_id,name,age,phone ,dept,enrolled_date = line.split(",")
        data["id"].append(staff_id)
        data["name"].append(name)
        data["age"].append(age)
        data["phone"].append(phone)
        data["dept"].append(dept)
        data["enrolled_date"].append(enrolled_date)
    # print(data)
    return(data)

def save_db():
    """
    把内存数据存回硬盘
    :return: 
    """
    f = open("%s.new"%DB_FILE,"w",encoding="utf-8")
    for index,staff_id in enumerate(STAFF_DATA['id']):
        row = []
        for col in COLUMNS:
            row.append(STAFF_DATA[col][index])
        f.write(",".join(row))
    f.close()

    os.remove(DB_FILE)
    os.rename("%s.new"%DB_FILE,DB_FILE)



STAFF_DATA = load_db(DB_FILE) #程序启动就执行
# print(STAFF_DATA)
#{'id': ['1', '2', '3', '4', '5', '6', '7', '8', '9', '10'],
# 'name': ['Alex Li', 'Jack Wang', 'Rain Wang', 'Mack Qiao', 'Rachel Chen', 'Eric Liu', 'Chao Zhang', 'Kevin Chen', 'Shit Wen', 'Shanshan Du'],
# 'age': ['22', '28', '21', '44', '23', '19', '21', '22', '20', '26'],
# 'phone': ['13651054608', '13451024608', '13451054608', '15653354208', '13351024606', '18531054602', '13235324334', '13151054603', '13351024602', '13698424612'],
#  'dept': ['IT', 'HR', 'IT', 'Sales', 'IT', 'Marketing', 'Administration', 'Sales', 'IT', 'Operation'],
# 'enrolled_date': ['2013‐04‐01\n', '2015‐01‐07\n', '2017‐04‐01\n', '2016‐02‐01\n', '2013‐03‐16\n', '2012‐12‐01\n', '2011‐08‐08\n', '2013‐04‐01\n', '2017‐07‐03\n', '2017‐07‐02']}


def op_gt(column,condtion_val):
    #
    # :param column:
    # :param condtion:
    # :return:
    #
    matched_records = []
    for index, val in enumerate( STAFF_DATA[column]):
        if float(val) > float(condtion_val):
            # print("match",val)
            # matched_records.append(STAFF_DATA['id'][index])
            # matched_records.append(STAFF_DATA['name'][index])
            # matched_records.append(STAFF_DATA['age'][index])
            record = []
            for col in COLUMNS:
                record.append( STAFF_DATA[col][index] )
            matched_records.append(record)
    return matched_records
    #print("matched_records:", matched_records)
# matched_records:
#  [['2', 'Jack Wang', '28', '13451024608', 'HR', '2015‐01‐07\n'],
#  ['4', 'Mack Qiao', '44', '15653354208', 'Sales', '2016‐02‐01\n'],
# ['5', 'Rachel Chen', '23', '13351024606', 'IT', '2013‐03‐16\n'],
# ['10', 'Shanshan Du', '26', '13698424612', 'Operation', '2017‐07‐02']]

    #return matched_records
        # else:
        #     print("没有",val,condtion,type(val),type(condtion))


def op_lt(column,condtion_val):
    matched_records = []
    for index, val in enumerate( STAFF_DATA[column]):
        if float(val) < float(condtion_val):
            record = []
            for col in COLUMNS:
                record.append( STAFF_DATA[col][index] )
            matched_records.append(record)
    # print("matched_records:", matched_records)
    return  matched_records

def op_eg(column,condtion_val):
    matched_records = []
    for index, val in enumerate( STAFF_DATA[column]):
        if val == condtion_val:
            record = []
            for col in COLUMNS:
                record.append( STAFF_DATA[col][index] )
            matched_records.append(record)
    return  matched_records
    # print("matched_records:", matched_records)

def op_like(column,condtion_val):
    matched_records = []
    for index, val in enumerate( STAFF_DATA[column]):
        if   condtion_val in val:
            record = []
            for col in COLUMNS:
                record.append( STAFF_DATA[col][index] )
            matched_records.append(record)
    return matched_records
    # print("matched_records:", matched_records)

def syntax_where(clause):
    #
    # 解析where条件
    # :param clause:
    # :return:
    #
    operators = {
        '>':op_gt,
        '<':op_lt,
        '=':op_eg,
        'like':op_like
    }
    for op_key ,op_func in operators.items(): 
        if op_key in clause:
            #print("clause:",clause)
            column,condtion = clause.split(op_key)
            # print("look")
            # print(column)
            # print(condtion)
            # print("look2")
            matched_data = op_func(column.strip(),condtion.strip())
            # print(matched_data)
            # break
            return  matched_data
    else: #只有在for执行完成，且中间没有被break情况下执行
        print_log("语法错误： where 条件只能支持[>,<,=,like ]",'error')


# find name , age  from staff_table where age > 22
def syntax_find(data_set,query_clause):
    """
    解析查询语言，从data_set中打印指定的列
    :param data_set:   [['2', 'Jack Wang', '28', '13451024608', 'HR', '2015‐01‐07\n'],#  ['4', 'Mack Qiao', '44', '15653354208', 'Sales', '2016‐02‐01\n'],# ['5', 'Rachel Chen', '23', '13351024606', 'IT', '2013‐03‐16\n'],
# ['10', 'Shanshan Du', '26', '13698424612', 'Operation', '2017‐07‐02']]    :param query_clause:  fina name ,age from staff_table
    :return: 
    """
    filter_cols_tmp  = query_clause.split("from")[0][4:].split(',')
    filter_cols = [i.strip()  for i in filter_cols_tmp]
    if '*' in filter_cols[0]:
        print(tabulate(data_set,headers=filter_cols,tablefmt="grid"))
    # print(filter_cols)
    else:
        reformat_data_set = []
        for row in data_set:
            filtered_vales = []
            for col in filter_cols:
                col_index = COLUMNS.index(col)
                filtered_vales.append(row[col_index])
            reformat_data_set.append(filtered_vales)
        # for r in reformat_data_set:
        #     print(r)
        # print(tabulate(reformat_data_set))
        print(tabulate(reformat_data_set,headers= COLUMNS,tablefmt="gird"))
    print_log("查询到%s条数据！" % len(data_set))

def syntax_delete(data_set,query_clause):
    pass


#  update staff_table set dept=技术  where dept=技术部
# update staff_table set age=26 where name=Alex Li
# update staff_table set dept=技术部  where dept=IT
def syntax_update(data_set,query_clause):
    """
    :param data_set: 
    :param query_clause: 
    :return: 
    """
    formula_raw =  query_clause.split('set')
    if  len(formula_raw)>1:
        col_name ,new_val  = formula_raw[1].strip().split("=")
        # col_index = COLUMNS.index(col_name)
        # 循环data_set，取到每条记录的ID，拿着这个ID到staff_data[id]里找对应ID的索引，
        # 再拿这个索引，到STAFF_DATA[age]的列表中，修改对应索引的值
        print("dataset",data_set)
        for matched_row in data_set:
            staff_id  = matched_row[0]
            staff_id_index = STAFF_DATA['id'].index(staff_id)
            STAFF_DATA[col_name][staff_id_index] = new_val
        print(STAFF_DATA)
        print_log("成功修改了%s条数据！"% len(data_set))

        save_db()  #吧修改后的数据刷到硬盘上

    else:
        print_log("语法错误！未检测到set关键字","error")

def syntax_add(data_set,query_clause):
    pass


def syntax_parser(cmd):
    """
    :param cmd: 
    :return: 
    """
    syntax_list = {
        "find":syntax_find,
        "del":syntax_delete,
        "update":syntax_update,
        "add":syntax_add,
    }

    # find name,age from staff_table where age > 22
    if  cmd.split()[0] in ('find','add','del','update'):
        if 'where' in cmd:
            query_clause ,where_clause = cmd.split("where")
            # print("look")
            # print(query_clause,where_clause)
            matched_records = syntax_where(where_clause)
        else:
            matched_records = []
            for index,staff_id in enumerate(STAFF_DATA['id']):
                record = []
                for col in COLUMNS:
                    record.append(STAFF_DATA[col][index])
                matched_records.append(record)
            query_clause = cmd

        cmd_action = cmd.split()[0]
        if cmd_action in syntax_list:
            syntax_list[cmd_action](matched_records,query_clause)
        else:
            print_log("语法错误","error")


    else:
        print_log("语法错误：\n[find\\add\\del\\update][column,...]"
                  "from [staff_table][where][column][>...][condition] \n ",'error' )


def main():
    """
    让用户数据语句，并执行
    :return: 
    """
    while True:
        cmd = input("[请输入查询语句：]:").strip()
        if not cmd: continue
        syntax_parser(cmd)

main()